
* Set Directory
cd "... Replication Files"

use "Data/BC_PPD.dta", clear

keep if fy >= 2015
drop if fy == 2023
drop if fy == .

replace contrib_ee_other = 0 if contrib_ee_other == .
gen contrib_ee_tot = contrib_ee_regular + contrib_ee_other

replace contrib_er_state = 0 if contrib_er_state == .
replace contrib_er_other = 0 if contrib_er_other == .
gen contrib_er_excess = contrib_er_state + contrib_er_other
replace contrib_er_excess = . if contrib_er_regular == .

gen missing = contrib_er_tot == .
gen ex_missing = contrib_er_excess == .
gen ee_missing = contrib_ee_tot == .
gen req_missing = requiredcontribution == .
gen pay_missing = payroll == . | payroll == 0
replace uaalamortperiod_gasb = . if uaalamortperiod_gasb > 40
gen amort_missing = uaalamortperiod_gasb == .
gen discount_missing = blendeddiscountrate == .

rename statename state

collapse (sum) contrib_er_tot contrib_er_excess contrib_ee_tot payroll requiredcontribution missing ex_missing ee_missing req_missing pay_missing normcostamount_tot actliabilities_gasb amort_missing discount_missing (mean) uaalamortperiod_gasb blendeddiscountrate, by(govtname state fy)

gen perc_req = contrib_er_tot / requiredcontribution

by govtname: egen miss_tot = sum(missing)
by govtname: egen miss_ex = sum(ex_missing)
by govtname: egen ee_miss_tot = sum(ee_missing)
by govtname: egen req_miss_tot = sum(req_missing)
by govtname: egen pay_miss_tot = sum(pay_missing)

gen perc_pay_er = contrib_er_tot / payroll
gen perc_pay_ee = contrib_ee_tot / payroll

* Merge in population data
merge 1:1 govtname fy using "Data/Population_AllGovs.dta", keepus(population)
drop if _merge == 2
drop _merge

* Merge in CPI
* https://www.minneapolisfed.org/about-us/monetary-policy/inflation-calculator/consumer-price-index-1913-
merge m:1 fy using "Data/CPI Price Deflator.dta"
keep if _merge == 3
drop _merge

gen state_indicator = state == govtname

* Merge in data on state aid in order to get 2020 population variable
merge m:1 state using "Data/cleanedAidRepsPop_all.dta" 
drop if _merge == 2
drop _merge

* Get 2020 population for non-states
merge m:1 govtname using "Data/local_aid.dta"
drop if _merge == 2

gen reps_per_mil = all_reps/(pop_2020 / 1000000)

* Adjust number of reps for non-state govs
sort govtname fy
gen pandemic_pop = 0
replace pandemic_pop = population if fy == 2020
by govtname: egen population_2020 = max(pandemic_pop)

gen wider_aid_21 = state_agency_aid_21
replace wider_aid_21 = local_aid_21 if state_indicator == 0
gen wider_aid_22 = state_agency_aid_22
replace wider_aid_22 = local_aid_22 if state_indicator == 0

gen wider_aid_post = 0
replace wider_aid_post = (wider_aid_21 * 1000000 / population_2020) if fy == 2021
replace wider_aid_post = (wider_aid_22 * 1000000 / population_2020) if fy == 2022

gen alt_reps_per_mil = reps_per_mil
replace alt_reps_per_mil = reps_adj/(population_2020 / 1000000) if reps_adj != .

* Drop DC
drop if state == "District of Columbia"

* Deflate aid using CPI
foreach y in contrib_er_tot contrib_er_excess contrib_ee_tot payroll requiredcontribution actliabilities_gasb { 
	replace `y' = `y' * 255.7 / price_deflator 
}

sort state fy
gen cont_percap = contrib_er_tot / population

egen gov_group = group(govtname)
egen state_group = group(state)
xtset gov_group fy

gen excess_pop = contrib_er_excess * 1000 / l.population

sum reps_per_mil if fy == 2019 & state_indicator == 1, detail
gen high = reps_per_mil > r(p50)

sum wider_aid_post if fy == 2022, detail
gen high_aid = wider_aid_post > r(p50)
sort govtname fy
by govtname: egen above_aid = max(high_aid)

keep if fy >= 2016

save "Data/Descriptive Sample.dta", replace
 
 